import sqlite3
import os
import random
from datetime import datetime, timedelta

# 确保db目录存在
os.makedirs("c:\\code\\table_mcp\\db", exist_ok=True)

# 连接到数据库
conn = sqlite3.connect("c:\\code\\table_mcp\\db\\database.db")
cursor = conn.cursor()

# 创建sku表
cursor.execute('''
CREATE TABLE IF NOT EXISTS sku (
    sku_id TEXT PRIMARY KEY,
    product_name TEXT NOT NULL,
    description TEXT,
    brand TEXT,
    
    category TEXT NOT NULL,
    subcategory TEXT,
    
    unit_price REAL NOT NULL,
    min_order_quantity INTEGER NOT NULL,
    wholesale_price_tier1 REAL,
    wholesale_price_tier2 REAL,
    wholesale_price_tier3 REAL,
    retail_price REAL,
    
    stock_quantity INTEGER DEFAULT 0,
    stock_status TEXT CHECK(stock_status IN ('in_stock', 'low_stock', 'out_of_stock')),
    
    supplier TEXT,
    production_date TEXT,
    expiry_date TEXT,
    specifications TEXT,
    unit TEXT,
    origin TEXT
)
''')

# 文具类别和子类别
categories = {
    "书写工具": ["圆珠笔", "钢笔", "铅笔", "记号笔", "荧光笔", "白板笔", "毛笔"],
    "纸制品": ["笔记本", "便签纸", "复印纸", "信纸", "信封", "卡片", "海报纸"],
    "办公用品": ["订书机", "打孔器", "回形针", "文件夹", "文件盒", "胶带", "剪刀"],
    "学生用品": ["尺子", "橡皮", "铅笔盒", "书包", "计算器", "书签", "涂改液"],
    "美术用品": ["水彩笔", "蜡笔", "油画棒", "画笔", "调色盘", "素描本", "颜料"]
}

# 品牌列表
brands = ["晨光", "得力", "真彩", "白雪", "派克", "三菱", "斑马", "百乐", "施德楼", 
          "辉柏嘉", "马培德", "康颂", "文华", "爱好", "东亚", "凌美", "蒙恬"]

# 供应商列表
suppliers = ["上海文具有限公司", "广州办公用品批发中心", "深圳学生用品厂", "北京文化用品有限公司", 
             "杭州文具批发市场", "温州办公设备有限公司", "成都文具制造厂", "天津办公用品有限公司"]

# 产地列表
origins = ["上海", "广州", "深圳", "北京", "杭州", "温州", "成都", "天津", "宁波", "苏州"]

# 单位列表
units = ["支", "盒", "本", "包", "套", "个", "张", "卷", "把"]

# 库存状态
stock_statuses = ["in_stock", "low_stock", "out_of_stock"]

# 生成100条假数据
test_data = []
for i in range(1, 101):
    # 生成SKU ID
    sku_id = f"STY{i:04d}"
    
    # 随机选择类别和子类别
    category = random.choice(list(categories.keys()))
    subcategory = random.choice(categories[category])
    
    # 随机选择品牌
    brand = random.choice(brands)
    
    # 生成产品名称和描述
    product_name = f"{brand} {subcategory}"
    description = f"{brand}品牌{subcategory}，{category}类，优质文具用品"
    
    # 生成价格信息
    unit_price = round(random.uniform(1.0, 500.0), 2)
    min_order_quantity = random.randint(5, 100)
    wholesale_price_tier1 = round(unit_price * 0.9, 2)
    wholesale_price_tier2 = round(unit_price * 0.85, 2)
    wholesale_price_tier3 = round(unit_price * 0.8, 2)
    retail_price = round(unit_price * 1.5, 2)
    
    # 生成库存信息
    stock_quantity = random.randint(0, 1000)
    if stock_quantity == 0:
        stock_status = "out_of_stock"
    elif stock_quantity < 50:
        stock_status = "low_stock"
    else:
        stock_status = "in_stock"
    
    # 生成其他信息
    supplier = random.choice(suppliers)
    
    # 生成生产日期（过去1年内的随机日期）
    days_ago = random.randint(0, 365)
    production_date = (datetime.now() - timedelta(days=days_ago)).strftime('%Y-%m-%d')
    
    # 部分产品有保质期
    expiry_date = None
    if random.random() < 0.3:  # 30%的产品有保质期
        expiry_days = random.randint(365, 1095)  # 1-3年的保质期
        expiry_date = (datetime.now() - timedelta(days=days_ago) + timedelta(days=expiry_days)).strftime('%Y-%m-%d')
    
    # 生成规格信息
    if subcategory in ["圆珠笔", "钢笔", "铅笔", "记号笔"]:
        specifications = random.choice(["0.5mm", "0.7mm", "1.0mm", "黑色", "蓝色", "红色", "彩色套装"])
    elif subcategory in ["笔记本", "复印纸", "信纸"]:
        specifications = random.choice(["A4", "A5", "B5", "16K", "32K", "方格", "横线", "空白"])
    elif subcategory in ["文件夹", "文件盒"]:
        specifications = random.choice(["A4", "A5", "B5", "透明", "彩色", "可扩展"])
    else:
        specifications = random.choice(["标准", "迷你", "大号", "豪华", "经济装", "礼品装"])
    
    # 生成单位
    unit = random.choice(units)
    
    # 生成产地
    origin = random.choice(origins)
    
    # 添加到测试数据列表
    test_data.append((
        sku_id, product_name, description, brand, category, subcategory,
        unit_price, min_order_quantity, wholesale_price_tier1, wholesale_price_tier2, wholesale_price_tier3, retail_price,
        stock_quantity, stock_status, supplier, production_date, expiry_date, specifications, unit, origin
    ))

# 清空现有数据（如果需要）
cursor.execute("DELETE FROM sku")

# 插入测试数据
cursor.executemany('''
INSERT INTO sku (
    sku_id, product_name, description, brand, category, subcategory,
    unit_price, min_order_quantity, wholesale_price_tier1, wholesale_price_tier2, wholesale_price_tier3, retail_price,
    stock_quantity, stock_status, supplier, production_date, expiry_date, specifications, unit, origin
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
''', test_data)

# 提交更改并关闭连接
conn.commit()
conn.close()

print("文具批发商SKU表创建成功并添加了100条测试数据")